package servlet;

import model.Essay;
import model.User;
import util.DBUtils;

import javax.servlet.RequestDispatcher;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LoginServlet extends javax.servlet.http.HttpServlet {
    protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        System.out.println("成功访问loginservlet");
        String username=request.getParameter("username");
        String password=request.getParameter("password");
        if(username.equals("")|password.equals("")){
            System.out.println("用户名或密码为空");
            request.getSession().setAttribute("status","3");
            RequestDispatcher rd = request.getRequestDispatcher("login.jsp");
            rd.forward(request,response);
        }else {
            DBUtils db = new DBUtils();
            String condition = "username='"+username+"' and password='"+password+"'";
            List<String[]> list = db.find("user",new String[]{"uid","username","password","role_id"},condition);
            if(list.isEmpty()){
                request.getSession().setAttribute("status","0");
                request.getSession().setAttribute("msg","登录失败");
                RequestDispatcher rd = request.getRequestDispatcher("login.jsp");
                rd.forward(request,response);
            }else{
                System.out.println("登录成功");
                User u=new User();
                for (String[] arr : list) {
                    System.out.printf("uid:%s,uearname:%s,passsword:%s,role_id:%s \n",arr[0],arr[1],arr[2],arr[3]);
                    u.setUid(Integer.parseInt(arr[0]));
                    u.setUsername(arr[1]);
                    u.setPassword(arr[2]);
                    u.setRole_id(Integer.parseInt(arr[3]));
                }
                if(list.get(0)[3].equals("0")){
                    String[] fields = {"eid","title","create_time","update_time","eclass","content"};
                    System.out.println("此用户为普通用户");
                    condition = "uid="+u.getUid();
                    List<String[]> list1 = db.find("essay",fields,condition);
                    for (String[] arr : list1) {
                        System.out.printf("eid:%s,title:%s,create_time:%s,update_time:%s,eclass:%s,content:%s\n",arr[0],arr[1],arr[2],arr[3],arr[4],arr[5]);
                    }
                    if(list1.isEmpty()){
                        System.out.println("信息加载失败");
                    }else {
                       /* request.getSession().setAttribute("status","1");
                        request.getSession().setAttribute("msg","信息加载成功");*/
                    }
                    int[][] zanRead=new int[12][3];
                    try {
                        Connection conn=null;
                        PreparedStatement ptmt=null;
                        ResultSet rs=null;
                        Class.forName("com.mysql.cj.jdbc.Driver");//注册数据库驱动
                        String url = "jdbc:mysql://localhost:3306/boke?useSSL=false&serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8";
                        conn= DriverManager.getConnection(url, "root", "123456");//通过DriverManager获取数据库
                        String  sql="SELECT sum(zan_sum),sum(read_sum), MONTH(create_time)  FROM  essay WHERE uid=?  GROUP BY MONTH(create_time) ORDER BY  MONTH(create_time) DESC;";
                        ptmt=conn.prepareStatement(sql);
                        ptmt.setInt(1, u.getUid());
                        rs = ptmt.executeQuery();
                        int mouth=0;
                        while (rs.next()){
                            zanRead[mouth][0]=rs.getInt("sum(zan_sum)");
                            zanRead[mouth][1]=rs.getInt("sum(read_sum)");
                            zanRead[mouth][2]=rs.getInt("MONTH(create_time)");
                            mouth++;
                        }
                        for(int i=0;i<12;i++){
                            for (int j=0;j<3;j++){
                                System.out.print(zanRead[i][j]+"   ");
                            }
                            System.out.println();
                        }
                        rs.close();
                        ptmt.close();
                        conn.close();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }

                    request.getSession().setAttribute("user",u);
                    request.getSession().setAttribute("essayData",list1);
                    request.getSession().setAttribute("zanReadData",zanRead);
                    RequestDispatcher rd = request.getRequestDispatcher("management.jsp");
                    rd.forward(request,response);
                }else {
                    System.out.println("此用户为系统管理员");
                    List<Essay> essayList=new ArrayList();
                    List<User> userList=new ArrayList();
                    try {
                        Connection conn=null;
                        PreparedStatement ptmt=null;
                        ResultSet rs=null;
                        Class.forName("com.mysql.cj.jdbc.Driver");//注册数据库驱动
                        String url = "jdbc:mysql://localhost:3306/boke?useSSL=false&serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8";
                        conn= DriverManager.getConnection(url, "root", "123456");//通过DriverManager获取数据库
                        String  sql1="select e.*,u.username\n" +
                                "from essay e join `user` u\n" +
                                "on e.uid=u.uid\n";
                        ptmt=conn.prepareStatement(sql1);
                        rs = ptmt.executeQuery();
                            while (rs.next()){
                                Essay e=new Essay();
                                e.setEid(rs.getInt("eid"));
                                e.setTitle(rs.getString("title"));
                                e.setCreate_time(rs.getTimestamp("create_time"));
                                e.setUpdate_time(rs.getTimestamp("update_time"));
                                e.setEclass(rs.getString("eclass"));
                                e.setAuther(rs.getString("username"));
                                essayList.add(e);
                            }
                            for (Essay e:essayList){
                                System.out.println(e);
                            }
         ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                        String  sql2="select *\n" +
                                "from  `user` \n" ;
                        ptmt=conn.prepareStatement(sql2);
                        rs = ptmt.executeQuery();
                        while (rs.next()){
                            User U=new User();
                            U.setUid(rs.getInt("uid"));
                            U.setUsername(rs.getString("username"));
                            U.setPassword(rs.getString("password"));
                            U.setModifytime(rs.getTimestamp("modifytime"));
                            U.setCreatetime(rs.getTimestamp("createtime"));
                            U.setRole_id(rs.getInt("role_id"));
                            userList.add(U);
                        }
                        for (User e:userList){
                            System.out.println(e);
                        }
                        rs.close();
                        ptmt.close();
                        conn.close();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    request.getSession().setAttribute("essayList",essayList);
                    request.getSession().setAttribute("userList",userList);
                    RequestDispatcher rd = request.getRequestDispatcher("management_admin.jsp");
                    rd.forward(request,response);
                }

            }
        }
    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
    doPost(request,response);
    }
}
